Creating a [materialised]view from generic data in Oracle/Mysql

Posted by Andrew White on Stack Overflow See other posts from Stack Overflow or by Andrew White
Published on 2010-06-18T00:51:18Z Indexed on 2010/06/18 1:03 UTC
Read the original article Hit count: 280

Filed under:
|
|
|
|

I have a generic datamodel with 3 tables

CREATE TABLE Properties 
(
  propertyId int(11) NOT NULL AUTO_INCREMENT,
  name varchar(80) NOT NULL
)
CREATE TABLE Customers
(
  customerId int(11) NOT NULL AUTO_INCREMENT,
  customerName varchar(80) NOT NULL
)
CREATE TABLE PropertyValues
(
  propertyId int(11) NOT NULL,
  customerId int(11) NOT NULL,
  value varchar(80) NOT NULL
)
INSERT INTO Properties VALUES (1, 'Age');
INSERT INTO Properties VALUES (2, 'Weight');
INSERT INTO Customers VALUES (1, 'Bob');
INSERT INTO Customers VALUES (2, 'Tom');
INSERT INTO PropertyValues VALUES (1, 1, '34');
INSERT INTO PropertyValues VALUES (2, 1, '80KG');
INSERT INTO PropertyValues VALUES (1, 2, '24');
INSERT INTO PropertyValues VALUES (2, 2, '53KG');

What I would like to do is create a view that has as columns all the ROWS in Properties and has as rows the entries in Customers. The column values are populated from PropertyValues. e.g.

 customerId Age   Weight
 1          34    80KG
 2          24    53KG

I'm thinking I need a stored procedure to do this and perhaps a materialised view (the entries in the table "Properties" change rarely). Any tips?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql